﻿using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace EFCoreDemo
{
    public class EFCore : IMiddleware
    {
        public async Task InvokeAsync(HttpContext context, RequestDelegate next)
        {
            context.Response.ContentType = "text/plain; charset=utf-8";  //防止出现中文乱码

            ////1、添加数据
            //using (var db = new StudentContext())
            //{
            //    var student = new Students { Sno = "10006", Sname = "Tom", StuGrage = 2024, Birthday = new DateTime(2005, 10, 1), };
            //    db.Students.Add(student);
            //    await db.SaveChangesAsync();
            //}


            ////2、更新数据
            //using (var db = new StudentContext())
            //{
            //    var student = await db.Student
            //    .FirstOrDefaultAsync(s => s.Sno == "10002");
            //    if (student != null)
            //    {
            //        student.Sname = "张四";
            //        await db.SaveChangesAsync();
            //    }
            //}
            ////3、删除数据
            //using (var db = new StudentContext())
            //{
            //    var student = await db.Student
            //    .FirstOrDefaultAsync(s => s.Sno == "10006");
            //    if (student != null)
            //    {
            //        db.Student.Remove(student);
            //        await db.SaveChangesAsync(); 

            //    }
            //}

            ////4、查询数据
            //using (var db = new StudentContext())
            //{
            //    var students = await db.Student
            //    .ToListAsync();
            //    foreach (var student in students)
            //    {
            //        await context.Response.WriteAsync($"{student.Sno} {student.Sname} {student.Birthday} {student.StuGrage} {student.Check_Order}\n");
            //    }
            //}

            //4、查询数据
            using (var db = new StudentContext())
            {
                var teachers = await db.T
                .ToListAsync();
                foreach (var teacher in teachers)
                {
                    await context.Response.WriteAsync($"{teacher.TeacherNo} {teacher.TeacherName} {teacher.Sex} {teacher.Department} {teacher.Profession}\n");
                }
            }
        }
    }


    public class StudentContext : DbContext
    {
        public DbSet<Student> Student { get; set; }
        public DbSet<Teacher> T { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                @"Data Source=.;Initial Catalog=Teach;User ID=sa;Password=123456;Encrypt=False");
        }
    }

    public class Student
    {
        /*
         *1、如不加注解：实体类的属性名（忽略大小写）、数据类型必须与数据库表一致
         *2、加注解后（Column注解）：实体类的属性名、数据类型可以与数据库表不一致
         */


        [Key]
        //[Column("sno", TypeName = "nchar(10)")]
        //[Required]
        //[MaxLength(10)]
        public string Sno { get; set; }

        [Column("sname", TypeName = "text")]
        public string Sname { get; set; } = "无名氏";

        //[Column("birthday", TypeName = "date")]
        public DateTime? Birthday { get; set; }

        [Column("stu_grage", TypeName = "int")]
        public short StuGrage { get; set; }

        //[Column("check_order")]
        //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Check_Order { get; set; }
    }


        /// <summary>
        /// 教师信息实体类
        /// 对应数据库中的T表
        /// </summary>
        [Table("T")]
        public class Teacher
        {
            /// <summary>
            /// 教师编号 - 主键
            /// </summary>
            [Key]
            [Required(ErrorMessage = "教师编号是必填项")]
            [StringLength(10, ErrorMessage = "教师编号长度不能超过10个字符")]
            [Column("TNO", TypeName = "nchar(10)")]
            public required string TeacherNo { get; set; }

            /// <summary>
            /// 教师姓名
            /// </summary>
            [Required(ErrorMessage = "教师姓名是必填项")]
            [StringLength(50, ErrorMessage = "教师姓名长度不能超过50个字符")]
            [Column("TN", TypeName = "nvarchar(50)")]
            public required string TeacherName { get; set; }

            /// <summary>
            /// 性别 - 默认值为"男"
            /// </summary>
            [Required(ErrorMessage = "性别是必填项")]
            [StringLength(2, ErrorMessage = "性别长度不能超过2个字符")]
            [Column("Sex", TypeName = "nchar(2)")]
            [RegularExpression("^[男女]$", ErrorMessage = "性别只能是'男'或'女'")]
            public string Sex { get; set; } = "男";

            /// <summary>
            /// 年龄
            /// </summary>
            [Required(ErrorMessage = "年龄是必填项")]
            [Range(18, 70, ErrorMessage = "年龄必须在18到70岁之间")]
            [Column("Age")]
            public int Age { get; set; }

            /// <summary>
            /// 职称
            /// </summary>
            [Required(ErrorMessage = "职称是必填项")]
            [StringLength(10, ErrorMessage = "职称长度不能超过10个字符")]
            [Column("Prof", TypeName = "nchar(10)")]
            public required string Profession { get; set; }

            /// <summary>
            /// 工资
            /// </summary>
            [Required(ErrorMessage = "工资是必填项")]
            [Range(0, double.MaxValue, ErrorMessage = "工资不能为负数")]
            [Column("Sal", TypeName = "numeric(18,2)")]
            [DataType(DataType.Currency)]
            public decimal Salary { get; set; }

            /// <summary>
            /// 津贴
            /// </summary>
            [Required(ErrorMessage = "津贴是必填项")]
            [Range(0, double.MaxValue, ErrorMessage = "津贴不能为负数")]
            [Column("Comm", TypeName = "numeric(18,2)")]
            [DataType(DataType.Currency)]
            public decimal Commission { get; set; }

            /// <summary>
            /// 部门
            /// </summary>
            [Required(ErrorMessage = "部门是必填项")]
            [StringLength(20, ErrorMessage = "部门长度不能超过20个字符")]
            [Column("Dept", TypeName = "nchar(20)")]
            public required string Department { get; set; }
        }
    

}
